﻿CREATE PROCEDURE BILL.ValidateBill
	@id INT = NULL,

	@pay FLOAT = NULL,
	@sealed BIT = NULL,

	@checkExist BIT = 1
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--This prefix is used on all the prints of the procedure to tabulate them depending
		--its nested level.
		@procName VARCHAR(300),
		--The result of the procedure. If all goes ok it will be 0, else it will contain the code
		--of the error that has been thrown.
		@result INT


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@result = 0
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ ', @id: ' + ISNULL(CAST(@id AS VARCHAR), '')
		+ ', @pay: ' + ISNULL(CAST(@pay AS VARCHAR), '')
		+ ', @sealed: ' + ISNULL(CAST(@sealed AS VARCHAR), '')
		+ ', @checkExist: ' + ISNULL(CAST(@checkExist AS VARCHAR), '')


	-----------------------
	-- Transaction variables
	-----------------------
	DECLARE
		--This variable is used as a checkpoint for handle nested transactions.
		@rollbackPoint NCHAR(32),
		--Indicates if the transaction to be commited is owned by the current procedure.
		@ownTransaction BIT

	SET @ownTransaction = 0
		

	-----------------------
	-- Parameters defaults
	-----------------------
	IF (@checkExist IS NULL)
		SET @checkExist = 1


	-----------------------
	-- Validation
	-----------------------
	IF (@checkExist = 1)
	BEGIN
		IF ((@result = 0) AND (@id IS NULL))
		BEGIN
			RAISERROR('Faltan argumentos claves. Especificar %s.', 11, 1, '"Id"')
			SET @result = @@ERROR --Set @result with the current @@Error value.				
		END

		IF ((@result = 0) AND (NOT EXISTS(SELECT * FROM BILL.Bills WHERE Id = @id)))
		BEGIN
			RAISERROR('No existen facturas con id "%d".', 11, 1, @id)
			SET @result = @@ERROR --Set @result with the current @@Error value.				
		END
	END

	IF ((@result = 0) AND (@pay <= 0))
	BEGIN
		RAISERROR('El argumento "%s" debe ser mayor a cero.', 11, 1, 'Pago')
		SET @result = @@ERROR --Set @result with the current @@Error value.				
	END

	IF ((@result = 0) AND (@sealed = 0))
	BEGIN
		IF ((@result = 0) AND (@id IS NULL))
		BEGIN
			RAISERROR('Faltan argumentos claves. Especificar %s.', 11, 1, '"Id"')
			SET @result = @@ERROR --Set @result with the current @@Error value.				
		END

		IF ((@result = 0) AND (EXISTS(SELECT * FROM BILL.Bills WHERE Id = @id AND Sealed = 1)))
		BEGIN
			RAISERROR('La factura ya fue sellada.', 11, 1)
			SET @result = @@ERROR --Set @result with the current @@Error value.				
		END
	END

	-----------------------
	-- Finish procedure
	-----------------------
	IF (@result = 0)
		PRINT @finishEndMSG
	ELSE 
		PRINT @finishEndMSG + ' Result: ' + CAST(@result AS VARCHAR)

	RETURN @result
END

GO
GRANT EXECUTE
    ON OBJECT::BILL.ValidateBill TO [SystemPublic]
    AS [dbo];